package org.kjtc.exportService;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;

@SuppressWarnings({ "deprecation" })
public class ExportSubBranchCompanyBalance<T> {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcel(String title, String[] headers, Collection<T> dataset, ServletOutputStream servletOutputStream) throws Exception { 
		// 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);
        // 纸张大小、方向
        HSSFPrintSetup ps = sheet.getPrintSetup();    
        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)     
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型
        sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.0); // 上边距
        sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.0 ); // 下边距
        sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.0 ); // 左边距
        sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.0 ); // 右边距
        
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.WHITE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style.setWrapText(true);
        // 设置文字水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.AUTOMATIC.index);
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 11);
        // 加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style.setFont(font);
        
        
        // 生成一个样式  
        HSSFCellStyle style0 = workbook.createCellStyle();
        // 设置这些样式  
        style0.setFillForegroundColor(HSSFColor.WHITE.index);  
        style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style0.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style0.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style0.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style0.setWrapText(true);
        // 设置文字水平居中
        style0.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 设置文字垂直居中
        style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        HSSFDataFormat df = workbook.createDataFormat(); 
        
		HSSFCellStyle cellStyle_2 = workbook.createCellStyle();  
		cellStyle_2.setDataFormat(df.getFormat("##0.00"));
        // 设置这些样式  
		cellStyle_2.setFillForegroundColor(HSSFColor.WHITE.index);  
		cellStyle_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		cellStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		cellStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		cellStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		cellStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
		cellStyle_2.setWrapText(true);
        // 设置文字水平居中
		cellStyle_2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        // 设置文字垂直居中
		cellStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFCellStyle cellStyle_0 = workbook.createCellStyle();  
		cellStyle_0.setDataFormat(df.getFormat("##0"));
        // 设置这些样式  
		cellStyle_0.setFillForegroundColor(HSSFColor.WHITE.index);  
		cellStyle_0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		cellStyle_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		cellStyle_0.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		cellStyle_0.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		cellStyle_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
		cellStyle_0.setWrapText(true);
        // 设置文字水平居中
		cellStyle_0.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        // 设置文字垂直居中
		cellStyle_0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFCellStyle cellStyle_4 = workbook.createCellStyle();  
		cellStyle_4.setDataFormat(df.getFormat("##0.0000"));
        // 设置这些样式  
		cellStyle_4.setFillForegroundColor(HSSFColor.WHITE.index);  
		cellStyle_4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		cellStyle_4.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		cellStyle_4.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		cellStyle_4.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		cellStyle_4.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
		cellStyle_4.setWrapText(true);
        // 设置文字水平居中
		cellStyle_4.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        // 设置文字垂直居中
		cellStyle_4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        // 生成一个样式  
        HSSFCellStyle style_data = workbook.createCellStyle();
        // 设置这些样式  
        style_data.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_data.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_data.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style_data.setWrapText(true);
        // 设置文字水平居中
        style_data.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style_data.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_data = workbook.createFont();  
        font_data.setColor(HSSFColor.AUTOMATIC.index);
        font_data.setFontName("Arial");
        font_data.setFontHeightInPoints((short) 11);
        // 不加粗
        font_data.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式  
        style_data.setFont(font_data);
        
        sheet.setColumnWidth(0, 1200);
        sheet.setColumnWidth(1, 3200);
        sheet.setColumnWidth(2, 4800);
        sheet.setColumnWidth(3, 2800);
        sheet.setColumnWidth(4, 2000);
        sheet.setColumnWidth(5, 3200);
        sheet.setColumnWidth(6, 2400);
        sheet.setColumnWidth(7, 2000);
        sheet.setColumnWidth(8, 3500);
        sheet.setColumnWidth(9, 2000);
        sheet.setColumnWidth(10, 3500);
        sheet.setColumnWidth(11, 2000);
        sheet.setColumnWidth(12, 3500);
        sheet.setColumnWidth(13, 3500);
        
        // 生成一个样式  
        HSSFCellStyle style_title = workbook.createCellStyle();
        // 设置这些样式  
        style_title.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_title.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
//        style_title.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderRight(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_title.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_title = workbook.createFont();  
        font_title.setColor(HSSFColor.AUTOMATIC.index);  
        font_title.setFontHeightInPoints((short) 24);
        font_title.setFontName("Arial");
        // 加粗
        font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style_title.setFont(font_title);
        
        HSSFCellStyle cellStyle1 = workbook.createCellStyle();
        // 设置这些样式  
        cellStyle1.setFillForegroundColor(HSSFColor.WHITE.index);  
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle1.setWrapText(true);
        // 设置文字水平居中
        cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        
        // 产生title  
        HSSFRow row_title = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
        	HSSFCell cell = row_title.createCell(i);  
            cell.setCellStyle(style_title);
		}
        Cell cell_title = row_title.createCell(0);
        cell_title.setCellStyle(style_title);
        cell_title.setCellValue(title);
        // 合并单元格
        CellRangeAddress cra_title = new CellRangeAddress(0, 0, 0, headers.length - 1);
        //在sheet里增加合并单元格  
        sheet.addMergedRegion(cra_title);
        
        HSSFRow row_danwei = sheet.createRow(1);
        HSSFCell cell_danwei = row_danwei.createCell(headers.length - 1);
        cell_danwei.setCellValue("单位：元");
        CellRangeAddress cra_danwei = new CellRangeAddress(1, 1, 0, headers.length - 2);
        sheet.addMergedRegion(cra_danwei);
        
        // 产生标题行
        HSSFRow row_header = sheet.createRow(2);
        for (short i = 0; i < headers.length; i++) { 
            HSSFCell cell = row_header.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);
        }
        
        // 遍历集合数据，产生数据行  
        Iterator<T> it = dataset.iterator();  
        int index = 2;
        int rowNo = 0;
        String effectiveDTTM = null;
        int count = 1;
        int startRow = 3;
        double oilPrice = 0.0;
        List<Integer> rowCount = new ArrayList<Integer>();
        while (it.hasNext()){
        	index++;
        	rowNo++;
        	HSSFRow row = sheet.createRow(index);
        	HSSFCell cell_No = row.createCell(0);
        	cell_No.setCellStyle(style_data);
        	cell_No.setCellValue(rowNo);
        	T t = (T) it.next();
        	// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];  
                String fieldName = field.getName();  
                String getMethodName = "get"  + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                if ("getStartChargingDTTM".equals(getMethodName)) {
					break;
				} else if ("getEffectiveDTTM".equals(getMethodName)) {
					Class tCls = t.getClass();  
	                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
	                Object value = getMethod.invoke(t, new Object[]{});
					if (effectiveDTTM == null) {
						effectiveDTTM = value.toString();
						HSSFCell cell = row.createCell(i + 1);
						cell.setCellStyle(style_data);
						cell.setCellValue(effectiveDTTM);
					} else {
						if (!effectiveDTTM.equals(value.toString())) {
							index++;
							rowCount.add(index + 1);
							HSSFRow rowNull = sheet.createRow(index);
							HSSFCell cell_Null = rowNull.createCell(0);
				        	cell_Null.setCellStyle(style_data);
				        	cell_Null.setCellValue("小         计");
							for (int j = 1; j < 5; j++) {
								HSSFCell cell = rowNull.createCell(j);
								cell.setCellStyle(style_data);
							}
							// 合并单元格
					        CellRangeAddress cra_title0 = new CellRangeAddress(index, index, 0, 4);
					        
					        //在sheet里增加合并单元格  
					        sheet.addMergedRegion(cra_title0);
					        // 合并单元格
					        cra_title0 = new CellRangeAddress(startRow, startRow + count - 1, 1, 1);
					        
					        //在sheet里增加合并单元格  
					        sheet.addMergedRegion(cra_title0);
					        
					        HSSFCell cell = rowNull.createCell(5);
					        cell.setCellStyle(cellStyle_2);
					        cell.setCellValue(oilPrice);
					        
					        cell = rowNull.createCell(6);
					        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
					        cell.setCellStyle(cellStyle_0);
					        cell.setCellFormula("SUM(G" + String.valueOf(startRow + 1) + ":G" + String.valueOf(startRow + count) + ")");
					        
					        cell = rowNull.createCell(7);
					        cell.setCellStyle(style_data);
					        cell.setCellValue("——");
					        
					        cell = rowNull.createCell(8);
					        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
					        cell.setCellStyle(cellStyle_4);
					        cell.setCellFormula("SUM(I" + String.valueOf(startRow + 1) + ":I" + String.valueOf(startRow + count) + ")");
					        
					        cell = rowNull.createCell(9);
					        cell.setCellStyle(style_data);
					        cell.setCellValue("——");
					        
					        cell = rowNull.createCell(10);
					        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
					        cell.setCellStyle(cellStyle_2);
					        cell.setCellFormula("SUM(K" + String.valueOf(startRow + 1) + ":K" + String.valueOf(startRow + count) + ")");
					        
					        cell = rowNull.createCell(11);
					        cell.setCellStyle(style_data);
					        cell.setCellValue("——");
					        
					        cell = rowNull.createCell(12);
					        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
					        cell.setCellStyle(cellStyle_2);
					        cell.setCellFormula("ROUND(SUM(M" + String.valueOf(startRow + 1) + ":M" + String.valueOf(startRow + count) + "), 2)");
					        
					        cell = rowNull.createCell(13);
					        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
					        cell.setCellStyle(cellStyle_2);
					        cell.setCellFormula("SUM(K" + String.valueOf(index + 1) + "+M" + String.valueOf(index + 1) + ")");

					        startRow = startRow + count + 2;
							count = 1;
							effectiveDTTM = value.toString();
							index++;
					        row = sheet.createRow(index);
					        rowNo = 1;
					        cell = row.createCell(0);
					        cell.setCellStyle(style_data);
					        cell.setCellValue(rowNo);
					        cell = row.createCell(1);
					        cell.setCellStyle(style_data);
					        cell.setCellValue(effectiveDTTM);
						} else {
							HSSFCell cell = row.createCell(i + 1);
							cell.setCellStyle(style_data);
							cell.setCellValue(effectiveDTTM);
							count++;
						}
					}
				} else {
	            	HSSFCell cell = row.createCell(i + 1);
	            	cell.setCellStyle(style_data);  
					Class tCls = t.getClass();  
	                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
	                Object value = getMethod.invoke(t, new Object[]{});
	                // 单元格赋值
	                setValue(value, cell, workbook, getMethodName, cellStyle1, cellStyle_2, cellStyle_4, cellStyle_0);
					if("getOilPrice".equals(getMethodName)) {
						oilPrice = (double) value;
					}
				}
			}
        }

        sheet.addMergedRegion(new CellRangeAddress(startRow, startRow + count - 1, 1, 1));
        startRow++;
        HSSFRow rowNull = sheet.createRow(index + 1);
    	HSSFCell cell_Null = rowNull.createCell(0);
    	cell_Null.setCellStyle(style_data);
    	cell_Null.setCellValue(rowNo + 1);
        HSSFRow row_total = sheet.createRow(index + 2);
        rowCount.add(index + 3);
        HSSFCell cell0 = row_total.createCell(0);
        cell0.setCellStyle(style_data);
        cell0.setCellValue("小         计");
        for (int j = 1; j < 5; j++) {
            cell0 = row_total.createCell(j);
            cell0.setCellStyle(style_data);
		}
        sheet.addMergedRegion(new CellRangeAddress(index + 2, index + 2, 0, 4));
        
        HSSFCell cell = row_total.createCell(5);
        cell.setCellStyle(cellStyle_2);
        cell.setCellValue(oilPrice);
        
        cell = row_total.createCell(6);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellStyle(cellStyle_0);
        cell.setCellFormula("SUM(G" + String.valueOf(startRow) + ":G" + String.valueOf(startRow + count - 1) + ")");
        
        cell = row_total.createCell(7);
        cell.setCellStyle(style_data);
        cell.setCellValue("——");
        
        cell = row_total.createCell(8);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellStyle(cellStyle_4);
        cell.setCellFormula("SUM(I" + String.valueOf(startRow) + ":I" + String.valueOf(startRow + count - 1) + ")");
        
        cell = row_total.createCell(9);
        cell.setCellStyle(style_data);
        cell.setCellValue("——");
        
        cell = row_total.createCell(10);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellStyle(cellStyle_2);
        cell.setCellFormula("SUM(K" + String.valueOf(startRow) + ":K" + String.valueOf(startRow + count - 1) + ")");
        
        cell = row_total.createCell(11);
        cell.setCellStyle(style_data);
        cell.setCellValue("——");
        
        cell = row_total.createCell(12);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellStyle(cellStyle_2);
        cell.setCellFormula("ROUND(SUM(M" + String.valueOf(startRow) + ":M" + String.valueOf(startRow + count - 1) + "), 2)");
        
        cell = row_total.createCell(13);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellStyle(cellStyle_2);
        cell.setCellFormula("SUM(K" + String.valueOf(index+3) + "+M" + String.valueOf(index+3) + ")");
        
        row_total = sheet.createRow(index + 3);
        String totalKWH = "";
        String balanceKWH = "";
        String sum = "";
        String service = "";
        int length = rowCount.size();
        for (int i = 0; i < length; i++) {
        	if(sum.equals("")) {
        		totalKWH = totalKWH + "G" + String.valueOf(rowCount.get(i));
        		balanceKWH = balanceKWH + "I" + String.valueOf(rowCount.get(i));
        		sum = sum + "K" + String.valueOf(rowCount.get(i));
        		service = service + "M" + String.valueOf(rowCount.get(i));
        	} else {
        		totalKWH = totalKWH + "+G" + String.valueOf(rowCount.get(i));
        		balanceKWH = balanceKWH + "+I" + String.valueOf(rowCount.get(i));
        		sum = sum + "+K"  + String.valueOf(rowCount.get(i));
        		service = service + "+M" + String.valueOf(rowCount.get(i));
        	}
		}
        cell0 = row_total.createCell(0);
        cell0.setCellStyle(style_data);
        cell0.setCellValue("合         计");
        for (int j = 1; j < 5; j++) {
            cell0 = row_total.createCell(j);
            cell0.setCellStyle(style_data);
		}
        sheet.addMergedRegion(new CellRangeAddress(index + 3, index + 3, 0, 4));
        
        cell0 = row_total.createCell(5);
        cell0.setCellStyle(style_data);
        cell0.setCellValue("——");
        
        cell0 = row_total.createCell(6);
        cell0.setCellStyle(cellStyle_0);
        cell0.setCellFormula("SUM(" + totalKWH + ")");
        
        cell0 = row_total.createCell(7);
        cell0.setCellStyle(style_data);
        cell0.setCellValue("——");
        
        cell0 = row_total.createCell(8);
        cell0.setCellStyle(cellStyle_4);
        cell0.setCellFormula("SUM(" + balanceKWH + ")");
        
        cell0 = row_total.createCell(9);
        cell0.setCellStyle(style_data);
        cell0.setCellValue("——");
        
        cell0 = row_total.createCell(10);
        cell0.setCellStyle(cellStyle_2);
        cell0.setCellFormula("ROUND(SUM(" + sum + "), 2)");
        
        cell0 = row_total.createCell(11);
        cell0.setCellStyle(style_data);
        cell0.setCellValue("——");
        
        cell0 = row_total.createCell(12);
        cell0.setCellStyle(cellStyle_2);
        cell0.setCellFormula("SUM(" + service + ")");
        
        cell0 = row_total.createCell(13);
        cell0.setCellStyle(cellStyle_2);
        cell0.setCellFormula("SUM(K" + (index + 4) + "+M" + (index + 4) + ")");
        
        int width = headers.length;
        HSSFRow row_shenhe = sheet.createRow(index + 4);
        row_shenhe.setHeightInPoints(80);
        for (int i = 0; i < headers.length; i++) {
        	HSSFCell cell_shenhe2 = row_shenhe.createCell(i);
            cell_shenhe2.setCellStyle(style0);
            cell_shenhe2.setCellValue("隆瑞三优财务（盖章）：                               分公司技术部（盖章） ：                                分公司财务（盖章）：        ");
		}
        sheet.addMergedRegion(new CellRangeAddress(index + 4, index + 4, 0, width - 1));        
//        for (int i = 0; i < headers.length; i++) {
//            sheet.autoSizeColumn(i, true);
//		}
        
        workbook.write(servletOutputStream);
        servletOutputStream.flush();
        servletOutputStream.close();
    }

	private void setValue(Object value, HSSFCell cell, HSSFWorkbook workbook, String getMethodName, HSSFCellStyle cellStyle1, HSSFCellStyle cellStyle_2, HSSFCellStyle cellStyle_4, HSSFCellStyle cellStyle_0) {
		if (value instanceof Integer) {
			if ("getChargingStationCarCount".endsWith(getMethodName)) {
				cell.setCellValue("");
			} else {
				int intValue = (Integer) value;
				cell.setCellValue(intValue);
			}
		} else if (value instanceof Double) {
			double dValue = (Double) value;
			cell.setCellValue(dValue);
			if ("getTotalChargingKWH".equals(getMethodName)) {
	            cell.setCellStyle(cellStyle_0);
			} else if ("getOilPrice".equals(getMethodName)) {
				cell.setCellStyle(cellStyle_2);
			} else {
				cell.setCellValue(dValue);
				cell.setCellStyle(cellStyle_4);
			}
		} else if (value instanceof String) {
			String strValue = value.toString();
			cell.setCellValue(strValue);
		}
	}
	
    public static int stringNumbers(String str, String des) {
    	int counter = 0;
    	while (str.indexOf(des) != -1) {
    		counter++;
    		str = str.substring(str.indexOf(des) + des.length());
    	}
        return counter;
    }
    
    public Date getSpecifiedDayBefore(Date date) {//可以用new Date().toLocalString()传递参数  
        Calendar c = Calendar.getInstance(); 
        c.setTime(date);  
        int day = c.get(Calendar.DATE);  
        c.set(Calendar.DATE, day - 1);  
   
        return c.getTime();  
    }
    
	public Double round(Double value) {
		BigDecimal b = new BigDecimal(Double.toString(value));
		return b.setScale(4, RoundingMode.DOWN).doubleValue();
	}
}
